﻿/**
* CRL
*/
using CRL.Data.DBAccess;
using CRL.Data.LambdaQuery;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace CRL.Data.DBExtend.RelationDB
{
    public sealed partial class DBExtend
    {
        
        #region 按存储过程
        /// <summary>
        /// 按编译
        /// </summary>
        /// <param name="query1"></param>
        /// <returns></returns>
        CallBackDataReader GetSpPageReader(LambdaQueryBase query1)
        {
            //var query1 = query as RelationLambdaQuery<TModel>;
            CheckTableCreated(query1.__MainType);
            //var fields = query.GetQueryFieldString(b => b.Length > 500 || b.PropertyType == typeof(byte[]));
            var fields = query1.GetQueryFieldString();
            var rowOver = query1.GetOrder();
            if (string.IsNullOrEmpty(rowOver))
            {
                var table = TypeCache.GetTable(query1.__MainType);
                if (table.PrimaryKey == null)
                {
                    throw new Exception("分页缺少默认排序字段");
                }
                rowOver = string.Format("t1.{0} desc", _DBAdapter.KeyWordFormat(table.PrimaryKey.MapingName));
            }
            var orderBy = Regex.Replace(rowOver, @"t\d\.", "t.");
            //var sb = new StringBuilder();
            //query1.GetQueryConditions(sb);
            //var condition = sb.ToString();
            var condition = Core.StringBuilderCache.GetSimpleString(sb =>
            {
                query1.GetQueryConditions(sb);
            });

            condition = _DBAdapter.SqlFormat(condition);
            query1.FillParames(this);
            var pageIndex = query1.PageIndex;
            var pageSize = query1.TakeNum;
            pageIndex = pageIndex == 0 ? 1 : pageIndex;
            pageSize = pageSize == 0 ? 15 : pageSize;
            AddParame("pageIndex", pageIndex);
            AddParame("pageSize", pageSize);
            AddOutParam("count", -1);
            Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("fields", fields);
            dic.Add("sort", orderBy);
            dic.Add("rowOver", rowOver);
            //dic.Add("pageSize", pageSize.ToString());
            //string sql = string.Format("{0} with(nolock) where {1}", tableName, where);
            var db = GetDBHelper(DataAccessType.Read);
            string sp = CompileSqlToSp(_DBAdapter.TemplatePage, condition, dic);
            CallBackDataReader reader;
            reader = new CallBackDataReader(db.RunDataReader(sp), () =>
            {
                return GetOutParam<int>("count");
            },sp);
            ClearParame();
            query1.ExecuteTime += db.ExecuteTime;
            return reader;
        }

        internal string GetPageReaderSql(LambdaQueryBase query1, out DBHelper db, out string condition)
        {
            CheckTableCreated(query1.__MainType);
            var fields = query1.GetQueryFieldString();
            var rowOver = query1.GetOrder();
            if (string.IsNullOrEmpty(rowOver))
            {
                var table = TypeCache.GetTable(query1.__MainType);
                if (table.PrimaryKey == null)
                {
                    throw new Exception("分页缺少默认排序字段");
                }
                rowOver = string.Format("t1.{0} desc", _DBAdapter.KeyWordFormat(table.PrimaryKey.MapingName));
            }
            rowOver = _DBAdapter.SqlFormat(rowOver);
            var orderBy = rowOver;
            //var sb = new StringBuilder();
            //query1.GetQueryConditions(sb);
            //condition = sb.ToString();
            condition = Core.StringBuilderCache.GetSimpleString(sb =>
            {
                query1.GetQueryConditions(sb);
            });

            condition = _DBAdapter.SqlFormat(condition);
            query1.FillParames(this);

            var pageIndex = query1.PageIndex;
            var pageSize = query1.TakeNum;
            pageIndex = pageIndex <= 0 ? 1 : pageIndex;
            pageSize = pageSize <= 0 ? 15 : pageSize;
            //string countSql = string.Format("select count(*) {0}", condition);
            db = GetDBHelper(DataAccessType.Read);
            var start = pageSize * (pageIndex - 1) + 1;
            var end = start + pageSize - 1;
            string sql = _DBAdapter.PageSqlFormat(db, fields, rowOver, condition, start, end, orderBy);
            return sql;
        }
        /// <summary>
        /// GROUP和是否编译判断
        /// </summary>
        /// <param name="query1"></param>
        /// <returns></returns>
        internal CallBackDataReader GetPageReader(LambdaQueryBase query1)
        {
            if (query1.__Unions != null)
            {
                return GetUnionPageReader(query1);
            }
            else if (query1.__GroupFields != null)
            {
                return GetGroupPageReader(query1);
            }
            else if (_DBAdapter.CanCompileSP && query1.__CompileSp)
            {
                return GetSpPageReader(query1);
            }

            var sql = GetPageReaderSql(query1, out var db, out var condition);
            string countSql = string.Format("select count(*){0}", condition);
            int count = Convert.ToInt32(SqlStopWatch.ExecScalar(db, countSql));
            query1.ExecuteTime += db.ExecuteTime;
            query1.__RowCount = count;
            var reader = new CallBackDataReader(db.ExecDataReader(sql), () =>
            {
                return count;
            },sql);
            query1.ExecuteTime += db.ExecuteTime;
            ClearParame();
            return reader;
        }

        #endregion

        #region group分页
        /// <summary>
        /// 按编译
        /// </summary>
        /// <param name="query1"></param>
        /// <returns></returns>
        CallBackDataReader GetSpGroupPageReader(LambdaQueryBase query1)
        {
            //var query1 = query as RelationLambdaQuery<TModel>;
            CheckTableCreated(query1.__MainType);
            //var sb = new StringBuilder();
            //query1.GetQueryConditions(sb);
            //var conditions = sb.ToString();
            var conditions = Core.StringBuilderCache.GetSimpleString(sb =>
            {
                query1.GetQueryConditions(sb);
            });
            var fields = query1.GetQueryFieldString();
            if (!conditions.Contains("group"))
            {
                throw new Exception("缺少group语法");
            }
            var rowOver = query1.GetOrder();
            if (string.IsNullOrEmpty(rowOver))
            {
                throw new Exception("Group分页需指定Group排序字段");
                //var table = TypeCache.GetTable(typeof(T));
                //rowOver = string.Format("t1.{0} desc", table.PrimaryKey.Name);
            }
            //var sort1 = System.Text.RegularExpressions.Regex.Replace(rowOver, @"t\d\.", "");
            conditions = _DBAdapter.SqlFormat(conditions);

            query1.FillParames(this);
            var pageIndex = query1.PageIndex;
            var pageSize = query1.TakeNum;
            pageIndex = pageIndex == 0 ? 1 : pageIndex;
            pageSize = pageSize == 0 ? 15 : pageSize;
            AddParame("pageIndex", pageIndex);
            AddParame("pageSize", pageSize);
            var db = GetDBHelper(DataAccessType.Read);
            db.AddOutParam("count", -1);
            var templateParame = new Dictionary<string, string>();
            templateParame.Add("fields", fields);
            templateParame.Add("rowOver", rowOver);
            string sp = CompileSqlToSp(_DBAdapter.TemplateGroupPage, conditions, templateParame);
            CallBackDataReader reader;
            reader = new CallBackDataReader(db.RunDataReader(sp), () =>
            {
                return GetOutParam<int>("count");
            },sp);
            query1.ExecuteTime += db.ExecuteTime;
            ClearParame();
            return reader;
        }

        internal string GetGroupPageReaderSql(LambdaQueryBase query1, out DBHelper db, out string condition)
        {
            CheckTableCreated(query1.__MainType);
            //var sb = new StringBuilder();
            //query1.GetQueryConditions(sb);
            //condition = sb.ToString();
            condition = Core.StringBuilderCache.GetSimpleString(sb =>
            {
                query1.GetQueryConditions(sb);
            });
            var fields = query1.GetQueryFieldString();
            if (!condition.Contains("group"))
            {
                throw new Exception("缺少group语法");
            }
            var rowOver = query1.GetOrder();
            if (string.IsNullOrEmpty(rowOver))
            {
                throw new Exception("Group分页需指定Group排序字段");
                //var table = TypeCache.GetTable(typeof(T));
                //rowOver = string.Format("t1.{0} desc", table.PrimaryKey.Name);
            }
            //var sort1 = System.Text.RegularExpressions.Regex.Replace(rowOver, @"t\d\.", "");
            condition = _DBAdapter.SqlFormat(condition);

            query1.FillParames(this);
            var pageIndex = query1.PageIndex;
            var pageSize = query1.TakeNum;
            pageIndex = pageIndex == 0 ? 1 : pageIndex;
            pageSize = pageSize == 0 ? 15 : pageSize;

            //string countSql = string.Format("select count(*)  from (select count(*) as a {0}) t", condition);
            db = GetDBHelper(DataAccessType.Read);
            query1.ExecuteTime += db.ExecuteTime;
            var start = pageSize * (pageIndex - 1) + 1;
            var end = start + pageSize - 1;
            string sql = _DBAdapter.PageSqlFormat(db, fields, rowOver, condition, start, end, "");
            return sql;
        }
        /// <summary>
        /// 按是否能编译
        /// </summary>
        /// <param name="query1"></param>
        /// <returns></returns>
        CallBackDataReader GetGroupPageReader(LambdaQueryBase query1)
        {
            //var query1 = query as RelationLambdaQuery<TModel>;
            if (_DBAdapter.CanCompileSP && query1.__CompileSp)
            {
                return GetSpGroupPageReader(query1);
            }
            var sql = GetGroupPageReaderSql(query1, out var db, out var condition);
            string countSql = string.Format("select count(*) from (select count(*) as a{0}) t", condition);
            //db = GetDBHelper(DataAccessType.Read);
            int count = Convert.ToInt32(SqlStopWatch.ExecScalar(db, countSql));
            query1.ExecuteTime += db.ExecuteTime;
            query1.__RowCount = count;
            var reader = new CallBackDataReader(db.ExecDataReader(sql), () =>
            {
                return count;
            },sql);
            query1.ExecuteTime += db.ExecuteTime;
            ClearParame();
            return reader;
        }
        #endregion

        #region union分页
        internal string GetUnionPageReaderSql(LambdaQueryBase query1, out DBHelper db, out string condition)
        {
            var fieldMapping = query1._CurrentSelectFieldCache.mapping;
            var unionPrex = "_tu";
            var fields = string.Join(",", fieldMapping.Select(b => $"{unionPrex}.{b.ResultName}"));
            var rowOver = query1.GetOrder();
            rowOver = Regex.Replace(rowOver, @"t\d+\.", $"{unionPrex}.");
            if (string.IsNullOrEmpty(rowOver))
            {
                var table = TypeCache.GetTable(query1.__MainType);
                if (table.PrimaryKey == null)
                {
                    throw new Exception("分页缺少默认排序字段");
                }
                rowOver = string.Format("t1.{0} desc", _DBAdapter.KeyWordFormat(table.PrimaryKey.MapingName));
            }
            rowOver = _DBAdapter.SqlFormat(rowOver);
            var orderBy = rowOver;
            var unionSql = query1.GetQuery(false);
            query1.FillParames(this);

            var pageIndex = query1.PageIndex;
            var pageSize = query1.TakeNum;
            pageIndex = pageIndex <= 0 ? 1 : pageIndex;
            pageSize = pageSize <= 0 ? 15 : pageSize;
            //string countSql = string.Format("select count(*) {0}", $" from ({unionSql}) {unionPrex}");
            db = GetDBHelper(DataAccessType.Read);

            var start = pageSize * (pageIndex - 1) + 1;
            var end = start + pageSize - 1;
            condition = $" from ({unionSql}) {unionPrex}";
            string sql = _DBAdapter.PageSqlFormat(db, fields, rowOver, condition, start, end, orderBy);
            return sql;
        }
        internal CallBackDataReader GetUnionPageReader(LambdaQueryBase query1)
        {
            //CheckTableCreated(query1.__MainType);
            var sql = GetUnionPageReaderSql(query1, out var db, out var condition);
            string countSql = string.Format("select count(*){0}", condition);
            //db = GetDBHelper(DataAccessType.Read);
            int count = Convert.ToInt32(SqlStopWatch.ExecScalar(db, countSql));
            query1.ExecuteTime += db.ExecuteTime;
            query1.__RowCount = count;

            var reader = new CallBackDataReader(db.ExecDataReader(sql), () =>
            {
                return count;
            }, sql);
            query1.ExecuteTime += db.ExecuteTime;
            ClearParame();
            return reader;
        }
        #endregion
    }
}
